# import matplotlib.pyplot as plt
# import numpy as np
# import pandas as pd
#
# data = {'name':['1','2'],'age':[21,46]}
# df = pd.DataFrame(data)
# print(df)
#
#
#
# series_one = pd.Series([1,2,3])
# series_two = pd.Series(['ONE','TWO','THERR'])
# df = pd.DataFrame({'one':series_one,'two':series_two})
# print(df)
#
# print(pd.__version__)
#
# series_instance = pd.Series(['1','2','3'],index=['one','second','therd'],name='num')
# print(series_instance)
# print(series_instance['second'])
#
# data = [['Google', 10], ['Runoob', 12], ['Wiki',13]]
# df = pd.DataFrame(data,columns=['one','second'])
# print(df)
# print(df.loc[0])
# df.to_csv('instance01.csv')
#
# series_name = pd.Series(['张三','李四','王五','赵六','郑七',
#                          '钱八','张千','赵六','李矛','张白','白九','冀二'],
#                         name='name')
# df = pd.merge(series_name,'score.csv',on='column_')
# #
# pd_01=pd.read_csv(r"C:\Users\l1530\Desktop\成绩表.csv")
# pd_02=pd.read_csv(r"C:\Users\l1530\Desktop\信息表.csv")
# # 1）给成绩表加上姓名列
# pd_03=pd.merge(pd_02,pd_01,on='学号')
# print(pd_03)
# # 2）给成绩表加上字段“总分”列，并求出总分
# pd_03['总分']=pd_03[['C#','线代','python']].replace('缺考',0).astype(int).sum(axis=1)
# print(pd_03)
# #3）增加列字段“等级”，标注每人的“优、良、中、及格、差”（90≤优，80≤良，70≤中，及格≤60，差≤60）；
# def get_level(score):
#     if score>=90:
#         return '优'
#     if 80<=score<=90 :
#         return '良'
#     if 70<=score<=80 :
#         return '中'
#     if 60<=score<=70 :
#         return '及格'
#     if score<=60:
#         return '差'
# pd_03['等级'] = pd_03['总分'].apply(get_level)
# print(pd_03)
# # 4）计算各门课程的平均成绩以及标准差；
# average = pd_03[['C#','线代','python']].replace('缺考',0).astype(int).mean()
# print(average)
# std = pd_03[['C#','线代','python']].replace('缺考',0).astype(int).std()
# print(std)
# # 5）做一总分成绩分布图，纵坐标表示成绩，横坐标表示学号，画出总分的均分横线和每人的总分圆点图。
# plt.figure(figsize=(10,6))
# plt.scatter(pd_03['学号'],pd_03['总分'],label='总分')
# average_ = pd_03['总分'].mean()
# plt.axhline(y=average_,color='r',linestyle='--',label='平均分')
# plt.xlabel='总分'
# plt.ylabel='学号'
# plt.title('总分分布图')
# plt.legend()
# plt.show()
#

import pandas as pd
import numpy as np


# 读取数据
b = pd.read_csv(r"C:\Users\l1530\Desktop\学习成绩.csv")

b=b.drop_duplicates()
b=b.fillna(0)
b['体育'] = np.where(b['体育'] == '作弊', 0, b['体育'])
b['军训'] = np.where(b['军训'] == '缺考', 0, b['军训'])
xml=b.select_dtypes(include=['object']).columns
for i in xml:
    b[i]=b[i].astype(str).str.strip()
l=['英语','体育','军训','数分','高代','解几']
for i in l:
    b[i]=b[i].astype(float)
def zf(b):
    return b['英语']+b['体育']+b['军训']+b['数分']+b['高代']+b['解几']
b['总分']=b.apply(zf,axis=1)
mi=b['总分'].min()
ma=b['总分'].max()
bins=[mi-1,400,450,ma+1]
labals=['一般','较好','优秀']
b['等级']=pd.cut(b['总分'],bins=bins,labels=labals)
print(b)
def biaozhun(s):
    v=s.mean()
    st=s.std()
    return (s-v)/st
l=['英语','体育','军训','数分','高代','解几']
for i in l:
    b[i+'标准差']=biaozhun(b[i])
s=[i for i in b.columns if i.endswith('标准差')]
def biaozhunsong(b):

    return b[s].sum()
b['标准差总分']=b.apply(biaozhunsong,axis=1)

bi=[b['标准差总分'].min()-1,b['标准差总分'].quantile(0.33),b['标准差总分'].quantile(0.66),b['标准差总分'].max()+1]
b['标准等级']=pd.cut(b['标准差总分'],bins=bi,labels=labals)
print(b)


